package xx.iverson.school_manage.userutil;

import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UpdateLetterField {

    private static final String DB_URL = "jdbc:mysql://localhost:3306/school_manage?useSSL=false&useUnicode=true&characterEncoding=utf8";
    private static final String USER = "root";
    private static final String PASS = "root";

    public static void updateLetters() {
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            String sql = "UPDATE user SET letter = ? WHERE userid = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);

            // 查询 role 为 1 的用户
            String selectSql = "SELECT * FROM user WHERE role = 1";
            try (PreparedStatement selectStmt = conn.prepareStatement(selectSql)) {
                ResultSet rs = selectStmt.executeQuery();

                while (rs.next()) {
                    int userId = rs.getInt("userid");
                    String realName = rs.getString("realname");
                    String letter = convertToPinyin(realName);

                    stmt.setString(1, letter);
                    stmt.setInt(2, userId);
                    stmt.executeUpdate();
                }
            }

            System.out.println("Letter field updated successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static String convertToPinyin(String name) {
        HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
        format.setCaseType(HanyuPinyinCaseType.LOWERCASE);
        format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);

        StringBuilder sb = new StringBuilder();
        String[] names = name.split("\\s+");
        for (String n : names) {
            char[] chars = n.toCharArray();
            boolean capitalizeNext = true;

            for (int i = 0; i < chars.length; i++) {
                char c = chars[i];
                if (c >= '\u4e00' && c <= '\u9fa5') {
                    try {
                        String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(c, format);
                        String pinyin = pinyinArray[0];
                        if (capitalizeNext) {
                            pinyin = Character.toUpperCase(pinyin.charAt(0)) + pinyin.substring(1);
                            capitalizeNext = false;
                        }
                        sb.append(pinyin);
                    } catch (BadHanyuPinyinOutputFormatCombination e) {
                        e.printStackTrace();
                    }
                } else {
                    if (capitalizeNext) {
                        c = Character.toUpperCase(c);
                        capitalizeNext = false;
                    }
                    sb.append(c);
                }
            }
        }

        return sb.toString().trim();
    }

    public static void main(String[] args) {
        updateLetters();
    }
}